﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GTCAS.DataAccess.Common
{
    class BaseDAO : IDisposable
    {
        /// <summary>
        /// Chuỗi kết nối CSDL
        /// </summary>
        private string _connectionString;

        private OracleConnection _oraConnection;

        private OracleCommand _oraCommand;
        /// <summary>
        /// Có điều khiển lỗi hay không
        /// </summary>
        private bool _handleErrors;
        /// <summary>
        /// Lỗi mới nhất
        /// </summary>
        private string _lastError;
        /// <summary>
        /// Có ghi log lỗi hay không
        /// </summary>
        private bool _logError;
        /// <summary>
        /// Tên file Log
        /// </summary>
        private string _logFile;

        /// <summary>
        /// Lấy thông tin đối tượng OracleConnection.
        /// </summary>
        /// <value>The connection.</value>
        public OracleConnection Connection
        {
            get
            {
                return _oraConnection;
            }
        }

        /// <summary>
        /// Khởi tạo đối tượng lớp <see cref="T:BaseDAO"/>.
        /// </summary>
        /// <param name="connectionstring">The connectionstring.</param>
        /// <param name="provider">The provider.</param>
        public BaseDAO(string connectionString)
        {
            _connectionString = connectionString;
            _oraConnection = new OracleConnection();
            _oraCommand = new OracleCommand();

            _oraConnection.ConnectionString = _connectionString;
            _oraCommand.Connection = _oraConnection;
        }
        /// <summary>
        /// Gets or sets a value indicating whether [handle errors].
        /// </summary>
        /// <value><c>true</c> if [handle errors]; otherwise, <c>false</c>.</value>
        public bool HandleErrors
        {
            get
            {
                return _handleErrors;
            }
            set
            {
                _handleErrors = value;
            }
        }

        /// <summary>
        /// Gets the last error.
        /// </summary>
        /// <value>The last error.</value>
        public string LastError
        {
            get
            {
                return _lastError;
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether [log errors].
        /// </summary>
        /// <value><c>true</c> if [log errors]; otherwise, <c>false</c>.</value>
        public bool LogErrors
        {
            get
            {
                return _logError;
            }
            set
            {
                _logError = value;
            }
        }

        /// <summary>
        /// Gets or sets the log file.
        /// </summary>
        /// <value>The log file.</value>
        public string LogFile
        {
            get
            {
                return _logFile;
            }
            set
            {
                _logFile = value;
            }
        }

        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        public OracleParameter AddParameter(string name, object value)
        {
            OracleParameter p = new OracleParameter();
            p.ParameterName = name;
            if (value == null)
                p.Value = DBNull.Value;
            else
                p.Value = value;
            return _oraCommand.Parameters.Add(p);
        }
        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="value">The type.</param>
        /// <returns></returns>
        public OracleParameter AddParameter(string name, OracleType dataType)
        {
            OracleParameter p = new OracleParameter();
            p.ParameterName = name;
            p.OracleType = dataType;
            return _oraCommand.Parameters.Add(p);
        }
        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="value">The type.</param>
        /// <returns></returns>
        public OracleParameter AddParameter(string name, OracleType dataType, int size)
        {
            OracleParameter p = new OracleParameter();
            p.ParameterName = name;
            p.OracleType = dataType;
            p.Size = size;
            return _oraCommand.Parameters.Add(p);
        }
        /// <summary>
        /// Adds the parameter.
        /// </summary>
        /// <param name="parameter">The parameter.</param>
        /// <returns></returns>
        public OracleParameter AddParameter(OracleParameter parameter)
        {
            return _oraCommand.Parameters.Add(parameter);
        }

        /// <summary>
        /// Gets the command.
        /// </summary>
        /// <value>The command.</value>
        public OracleCommand Command
        {
            get
            {
                return _oraCommand;
            }
        }

        /// <summary>
        /// Begins the transaction.
        /// </summary>
        public void BeginTransaction()
        {
            if (_oraConnection.State == System.Data.ConnectionState.Closed)
            {
                _oraConnection.Open();
            }
            _oraCommand.Transaction = _oraConnection.BeginTransaction();
        }

        /// <summary>
        /// Commits the transaction.
        /// </summary>
        public void CommitTransaction()
        {
            _oraCommand.Transaction.Commit();
            _oraConnection.Close();
        }

        /// <summary>
        /// Rollbacks the transaction.
        /// </summary>
        public void RollbackTransaction()
        {
            _oraCommand.Transaction.Rollback();
            _oraConnection.Close();
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, CommandType commandType)
        {
            return ExecuteNonQuery(query, commandType, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, CustomConnectionState connectionState)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionState);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string query, CommandType commandType, CustomConnectionState connectionState)
        {
            _oraCommand.CommandText = query;
            _oraCommand.CommandType = commandType;
            int i = -1;
            try
            {
                if (_oraConnection.State == System.Data.ConnectionState.Closed)
                {
                    _oraConnection.Open();
                }
                i = _oraCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                _oraCommand.Parameters.Clear();
                if (connectionState == CustomConnectionState.CloseOnExit)
                {
                    _oraConnection.Close();
                }
            }

            return i;
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandType)
        {
            return ExecuteScalar(query, commandType, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CustomConnectionState connectionState)
        {
            return ExecuteScalar(query, CommandType.Text, connectionState);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandType, CustomConnectionState connectionState)
        {
            _oraCommand.CommandText = query;
            _oraCommand.CommandType = commandType;
            object o = null;
            try
            {
                if (_oraConnection.State == System.Data.ConnectionState.Closed)
                {
                    _oraConnection.Open();
                }
                o = _oraCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                _oraCommand.Parameters.Clear();
                if (connectionState == CustomConnectionState.CloseOnExit)
                {
                    _oraConnection.Close();
                }
            }

            return o;
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, CommandType commandType)
        {
            return ExecuteReader(query, commandType, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, CustomConnectionState connectionState)
        {
            return ExecuteReader(query, CommandType.Text, connectionState);
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string query, CommandType commandType, CustomConnectionState connectionState)
        {
            _oraCommand.CommandText = query;
            _oraCommand.CommandType = commandType;
            DbDataReader reader = null;
            try
            {
                if (_oraConnection.State == System.Data.ConnectionState.Closed)
                {
                    _oraConnection.Open();
                }
                if (connectionState == CustomConnectionState.CloseOnExit)
                {
                    reader = _oraCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = _oraCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                _oraCommand.Parameters.Clear();
            }

            return reader;
        }

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandType)
        {
            return ExecuteDataSet(query, commandType, CustomConnectionState.CloseOnExit);
        }

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CustomConnectionState connectionState)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionState);
        }

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">The commandType.</param>
        /// <param name="connectionstate">The connectionstate.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandType, CustomConnectionState connectionState)
        {
            OracleDataAdapter adapter = new OracleDataAdapter();
            _oraCommand.CommandText = query;
            _oraCommand.CommandType = commandType;
            adapter.SelectCommand = _oraCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                _oraCommand.Parameters.Clear();
                if (connectionState == CustomConnectionState.CloseOnExit)
                {
                    if (_oraConnection.State == System.Data.ConnectionState.Open)
                    {
                        _oraConnection.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// Lấy kết quả dạng List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="commandType"></param>
        /// <param name="connectionState"></param>
        /// <returns></returns>
        public List<T> GetList<T>(string query, CommandType commandType, CustomConnectionState connectionState)
        {

            try
            {
                DbDataReader dr = ExecuteReader(query, commandType, connectionState);
                if (dr == null || dr.FieldCount == 0)
                    return null;
                int fCount = dr.FieldCount;
                Type m_Type = typeof(T);
                object obj;
                List<T> m_List = new List<T>();
                while (dr.Read())
                {
                    obj = Activator.CreateInstance(m_Type);
                    for (int i = 0; i < fCount; i++)
                    {
                        if (dr[i] != DBNull.Value)
                        {
                            var p = m_Type.GetProperty(dr.GetName(i));
                            if (p != null)
                            {

                                var type = p.PropertyType;
                                var value = Convert.ChangeType(dr[i], type);
                                p.SetValue(obj, value, null);
                            }
                        }
                    }
                    m_List.Add((T)obj);
                }
                dr.Close();
                return m_List;
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                _oraCommand.Parameters.Clear();
                if (connectionState == CustomConnectionState.CloseOnExit)
                {
                    if (_oraConnection.State == System.Data.ConnectionState.Open)
                    {
                        _oraConnection.Close();
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Handles the exceptions.
        /// </summary>
        /// <param name="ex">The ex.</param>
        private void HandleExceptions(Exception ex)
        {
            if (LogErrors)
            {
                WriteToLog(ex.Message);
            }
            else if (HandleErrors)
            {
                _lastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }

        /// <summary>
        /// Writes to log.
        /// </summary>
        /// <param name="msg">The MSG.</param>
        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            _oraConnection.Close();
            _oraConnection.Dispose();
            _oraCommand.Dispose();
        }
    }

    /// <summary>
    /// Trạng thái của connection
    /// </summary>
    public enum CustomConnectionState
    {
        KeepOpen,
        CloseOnExit
    }
}
